use master 
go

create database TBL
go

use TBL
go

create table Card 
(
	ID nvarchar(20) primary key ,
	PassWord nvarchar(10),
	Balance int ,
	UserName nvarchar(10)
)

create table Computer
(
	ID nvarchar(20) primary key,
	OnUse char(1) check(OnUse in (1,0)),
	note text ,
)

create table Record 
(
	ID int primary key ,
	Cardld nvarchar(20) references Card(ID),
	ComputerID nvarchar(20) references Computer(ID),
	BeginTime datetime,
	Endtime datetime,
	Fee int
)

insert into Card values 
('0023_ABC','555',98,'张军'),('0025_bbd','abe',300,'朱俊'),('036__CCD','何柳',100,'何柳'),
('0045_YGR','0045_YGR',58,'证验'),('0078_RJV','55885fg',600,'校庆'),('0089','zhang',134,'张峻')


insert into Computer values
('02','0','25555'),('03','1','55555'),('04','0','66666'),('05','1','88888'),
('06','0','688878'),('B01','0','558558')

insert into Record values 
(23,'0078_RJV','B01','2007-07-15 19:00:00','2007-07-15 21:00:00',20),
(34,'0025_bbd','02','2006-12-25 18:00:00','2006-12-25 22:00:00',23),
(45,'0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00',50),
(46,'0023_ABC','03','2006-12-22 15:26:00','2006-12-22 22:55:0',6),
(47,'0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00',50),
(48,'0023_ABC','03','2007-1-06 15:26:00','2007-1-06 22:55:0',6),
(55,'0023_ABC','03','2006-07-21 15:26:00','2006-07-21 22:55:00',50),
(64,'0045_YGR','04','2006-12-24 18:00:00','2006-12-24 22:00:00',3),
(65,'0025_bbd','02','2006-12-28 18:00:00','006-12-28 22:00:00',23),
(98,'0025_bbd','02','2006-12-26 18:00:00','2006-12-26 22:00:00',23)


select * from Card
select * from Computer
select * from Record
--1. 查询出用户名为'张军'的上网卡的上网记录，要求显示卡号，用户名，机器编号、开始时间、结束时间，和消费金额，并按消费金额降序排列
	Select R.Cardld 卡号,C.UserName 用户名,R.ComputerID 机器编号,R.BeginTime 开始时间,R.Endtime 结束时间,R.Fee 消费金额 from Record  R 
	inner join Card C on R.Cardld=C.ID where UserName='张军' order by Fee desc 
--2. 查询出每台机器上的上网次数和消费的总金额
	Select ComputerID,count(ComputerID) 上网次数,sum(Fee) 总金额 from Record group by ComputerID
--3. 查询出所有已经使用过的上网卡的消费总金额
	Select Cardld  卡号,Sum(Fee) 消费总金额 from Record group by Cardld 
--4. 查询出从未消费过的上网卡的卡号和用户名
	Select C.ID 卡号,C.UserName 用户名 from Record R right join Card C on R.Cardld=C.ID where fee is null or Fee=''
	
--5. 将密码与用户名一样的上网卡信息查询出来
	Select A.* from Card A,Card B where A.ID=B.PassWord  
--6. 查询出使用次数最多的机器号和使用次数
	Select C.ID 机器号,count(Cardld) 使用次数 from Record R inner join Computer C on R.ComputerID=C.ID group by C.ID
	 having C.ID=(Select top 1 ComputerID from Record group by ComputerID order by count(Cardld) desc )
--7. 查询出卡号是以'ABC'结尾的用户卡号，名，上网的机器号和消费金额
	Select C.ID 用户卡号,C.UserName 用户名,R.ComputerID 机器号, sum(Fee) 消费总金额 from Card C inner join Record R on C.ID=R.Cardld where C.ID like '%ABC' group by C.ID,C.UserName,R.ComputerID
--8. 查询出是周六、周天上网的记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
	Select C.ID 卡号,C.UserName 用户名,R.ComputerID 机器号,R.BeginTime 开始时间,R.Endtime 结束时间,fee 消费金额 from Record R 
	inner join Card C on R.Cardld=C.ID where datename(DW,R.BeginTime)='星期六' or datename(DW,R.BeginTime)='星期日'
--9. 查询成一次上网时间超过12小时的的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
	select R.Cardld 卡号,C.UserName 用户名,R.ComputerID 机器号, R.BeginTime 开始时间,R.Endtime 结束时间,fee 消费金额 from Record R 
	inner join Card C on R.Cardld=C.ID where datediff(hh,BeginTime,EndTime)>12
	--10. 查询除消费金额排列前三名(最高)的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
	Select top 3 R.Cardld 卡号,C.UserName 用户名,R.ComputerID 机器号, R.BeginTime 开始时间, R.Endtime 结束时间, fee 消费金额 from Record R
	 inner join Card C on R.Cardld=C.ID order by Fee desc 